package extend.org.compiere.utils;

import java.awt.Desktop;
import java.io.*;
import java.net.InetAddress;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.LinkedHashMap;
import java.util.Properties;
import java.util.TimeZone;
import java.util.logging.Level;
import jxl.Sheet;
import jxl.Workbook;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.compiere.model.MAttachment;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MClient;
import org.compiere.model.MOrg;
import org.compiere.model.MProcess;
import org.compiere.model.MUser;
import org.compiere.model.Mhrmtimesheet;
import org.compiere.model.PO;
import org.compiere.model.X_C_Period;
import org.compiere.process.ProcessInfo;
import extend.org.compiere.hrm.ExcelCell;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.EMail;
import org.compiere.util.Env;
import org.compiere.util.Language;
import org.compiere.util.Msg;
import org.compiere.utils.DigestOfFile;
import org.eevolution.model.X_HR_Employee;
import org.joda.time.DateTime;

/**
 * This class for utils
 * @author V.Sokolov
 *
 */
public class Util {
	
	private static int BUFSIZE = 8192;
	/**	Logger							*/
	protected static CLogger log = CLogger.getCLogger(Util.class);
	/** */
	public static String localFilePath = System.getProperty("java.io.tmpdir") + System.getProperty("file.separator");// + (Env.isWindows() == true ? "":"//");  //+ System.getProperty("file.separator");
	/** The maximum possible date in the system */
	public static DateTime MAX_DATE = new DateTime("3333-12-31"); 
	/** UNKNOWN  */
	public static final String UNKNOWN = "UNKNOWN";
	
	/**
	 * Displays a message indicating that the field is required
	 * @param fieldName
	 * Example: "Field Name required"
	 */
	public static String requiredField(String fieldName){
		return Msg.translate(Env.getCtx(), "Field")+" ["+Msg.translate(Env.getCtx(), fieldName)+"] "+Msg.translate(Env.getCtx(), "required");
		
	}
	
	/**
	 * Show a warning if the accumulated error (StringBuilder)
	 * @param errmsg
	 */
	public static void checkErrMsg(StringBuilder errmsg){
		if(errmsg.length() > 0)
			checkErrMsg(errmsg.toString());
	}
	
	/**
	 * Show a warning if the accumulated error (String)
	 * @param errmsg
	 */
	public static void checkErrMsg(String errmsg){
		if(errmsg.length() > 0)
			throw new AdempiereException(errmsg);
	}
	
	/**
	 * The number of days over a period of dates
	 * @param dbeg
	 * @param dend
	 * @return int (dend - dbeg)
	 */
	public static int getDays(Timestamp dbeg, Timestamp dend){
		
		if (dbeg.getTime() < dend.getTime()){
			return (int) ((dend.getTime() - dbeg.getTime()) / (24 * 60 * 60 * 1000));
		}else if(dbeg.getTime() == dend.getTime() )
			return 1;
		else 
			return -1;
	}

	/**
	 * The number of working days over a period of dates
	 * 
	 * @param BPartner_ID
	 * @param dbeg
	 * @param dend
	 * @return
	 */
	public static int getWorkingDays(int BPartner_ID, Timestamp dbeg, Timestamp dend){
		
		int days = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// Employee data from the card
				String sql_ = "SELECT COUNT(*) FROM HRM_SCHEDULESETTINGS_V WHERE AD_CLIENT_ID=? " +
						"AND C_BPARTNER_ID=? " +
						"AND DATEFROM BETWEEN "+DB.TO_DATE(dbeg, true)+" AND "+DB.TO_DATE(dend, true)+" \n"+
						"AND HOLIDAYS_ID IS NULL";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			pstmt.setInt(1, Env.getAD_Client_ID(Env.getCtx()));
			pstmt.setInt(2, BPartner_ID);
			rs = pstmt.executeQuery();
			if(rs.next())
				days = rs.getInt(1);
				
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return days;
	}
	
	/**
	 * The number of working days over a period of dates
	 * 
	 * @param BPartner_ID
	 * @param dbeg
	 * @param dend
	 * @return
	 */
	public static int getWorkDays(int BPartner_ID, Timestamp dbeg, Timestamp dend){
		
		int days = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// Employee data from the card
		String sql_ = "SELECT COUNT(*) FROM HRM_SCHEDULESETTINGS_V T \n" +
				"JOIN HRM_DAYSCHEDULE R ON R.HRM_DAYSCHEDULE_ID=T.WORKSCHEDULE_ONEDAYTYPE_ID \n" +
				"WHERE T.AD_CLIENT_ID=? \n" +
				"AND T.C_BPARTNER_ID=? \n" +
				"AND T.DATEFROM BETWEEN "+DB.TO_DATE(dbeg, true)+" AND "+DB.TO_DATE(dend, true)+" \n"+
				"AND T.HOLIDAYS_ID IS NULL \n" +
				"AND R.HRM_HOURQUOTA > 0";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			pstmt.setInt(1, Env.getAD_Client_ID(Env.getCtx()));
			pstmt.setInt(2, BPartner_ID);
			rs = pstmt.executeQuery();
			if(rs.next())
				days = rs.getInt(1);
				
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return days;
	}
	
	/**
	 * Calculate the number of complete years between the dates. You can calculate the age of the person.
	 * @param birthDay
	 * @param checkDay
	 * @return years
	 */
	public static int getAge(Timestamp birthDay, Timestamp checkDay){
		
		Calendar calendarBirthDay = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
		Calendar calendarCheckDay = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
		
		calendarBirthDay.setTimeInMillis(birthDay.getTime());
		calendarCheckDay.setTimeInMillis(checkDay.getTime());
		
		int years = calendarCheckDay.get(Calendar.YEAR) - calendarBirthDay.get(Calendar.YEAR);
		// корректируем, если текущий месяц в дате проверки меньше месяца даты рождения
		int checkMonth = calendarCheckDay.get(Calendar.MONTH);
		int birthMonth = calendarBirthDay.get(Calendar.MONTH);
		if ( checkMonth < birthMonth ) 
			years --;
		else  if (checkMonth == birthMonth
				&& calendarCheckDay.get(Calendar.DAY_OF_MONTH) < calendarBirthDay.get(Calendar.DAY_OF_MONTH)) 
			         // отдельный случай - в случае равенства месяцев,
			         // но меньшего дня месяца в дате проверки - корректируем
			         years --;
	     return years;
	}
	
	/**
	 * The number of month over a period of dates
	 * @param dbeg
	 * @param dend
	 * @return int (dend - dbeg)
	 */
	public static int getMonth(Timestamp dbeg, Timestamp dend){
		
		Calendar cbeg = Calendar.getInstance();
		cbeg.setTime(dbeg);
		Calendar cend = Calendar.getInstance();
		cend.setTime(dend);
		
		if (dend.getTime() < dbeg.getTime()){
			throw new AdempiereException(" Enddate not must be less than startdate ");
		}
		else if (cend.get(Calendar.YEAR) == cbeg.get(Calendar.YEAR) && cend.get(Calendar.MONTH) == cbeg.get(Calendar.MONTH)){
			return 0;		
		}else {
			return (int) (cend.get(Calendar.YEAR) - cbeg.get(Calendar.YEAR)) * 12 + cend.get(Calendar.MONTH) - cbeg.get(Calendar.MONTH);
		}
	}
	
	/**
     * Writing to a file stream
     *
     * @param src the source stream (closed after copying)
     * @param dst to copy (closed after copying)
     */
    public static void copyStream(InputStream src, File dst) throws Exception {
        BufferedInputStream fSrc = null;
        BufferedOutputStream fDst = null;
        try {
            fSrc = new BufferedInputStream(src);
            fDst = new BufferedOutputStream(new FileOutputStream(dst));
            byte[] b = new byte[BUFSIZE];
            int n;
            while ((n = fSrc.read(b)) > 0) {
                fDst.write(b, 0, n);
            }
        } finally {
            if (fSrc != null) {
                fSrc.close();
            }
            if (fDst != null) {
                fDst.close();
            }
        }
    }

    /**
	 * Know whether the date is a holiday
	 */
	public static boolean isHoliday(Timestamp timestamp){
			
		return getHolidayDayoff(timestamp) != 0 ? true : false;
	}
	
	/**
	 * We define the identifier ID of the holiday or weekend
	 */
	public static int getHolidayDayoff(Timestamp timestamp){
		
		DateTime dt = new DateTime(timestamp.getTime());
		int holidayID = 0;
		
		StringBuffer sql_ = new StringBuffer("select hrm_holidays_id from hrm_holidays_v where ");
		sql_.append(" ? = month and ");
		sql_.append("(").append(dt.getDayOfMonth()).append("-sdate <= countDays) and ");
		sql_.append("(").append(dt.getDayOfMonth()).append("-sdate >= 0) and ");
		sql_.append(" ? between startdate and enddate");
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			pstmt = DB.prepareStatement(sql_.toString(), null);
			pstmt.setInt(1, dt.getMonthOfYear());
			pstmt.setDate(2, new Date(timestamp.getTime()));
			rs = pstmt.executeQuery();
			if (rs.next())
				holidayID = rs.getInt(1);
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return holidayID;
	}
	
	/**
	 * Check interval start and end dates
	 * @param startdate
	 * @param enddate
	 */
	public static void checkInterval(Timestamp startdate, Timestamp enddate){
		
		if(startdate == null || enddate == null)
			return;
		
		if(startdate.getTime() > enddate.getTime())
			throw new AdempiereException(Msg.translate(Env.getCtx(), "incorrectInterval"));
		
	}
	
	/**
	 * Close periods
	 * 
	 * @param tableName
	 * @param listField If two or more fields Write the via , Example: 'table1,table2'
	 */
	public static void updPeriod(PO po, String listField){
		
		//
		StringBuilder sqlWhereSel = new StringBuilder(" where 1=1 \n");
		String[] fields = listField.split(",");
		for(int i=0; i<fields.length;i++){
			if(!fields[i].isEmpty()){
				sqlWhereSel.append("and ").append(fields[i]).append("=? \n");
				if(i == 0)
					listField = fields[i];
				else
					listField += ","+fields[i];
			}
		}
		
		StringBuilder sqlSel = new StringBuilder("select ");
		sqlSel.append(listField);
		sqlSel.append(", startdate, \n");
		sqlSel.append("coalesce(enddate,to_date('31.12.3333','dd.mm.yyyy'),enddate) as enddate, \n");
		sqlSel.append("case when enddate is null then 'Y' else 'N' end as isactive, \n");
		sqlSel.append("case when enddate is null then 'N' else 'Y' end as processed \n");
		sqlSel.append("from (select startdate, \n lead(startdate,1) over (partition by ");
		sqlSel.append(listField); 
		sqlSel.append(" order by "); 
		sqlSel.append(listField);
		sqlSel.append(", startdate) as enddate, \n");
		sqlSel.append(listField);
		sqlSel.append("\n from ").append(po.get_TableName()).append(sqlWhereSel).append("group by ");
		sqlSel.append(listField);
		sqlSel.append("\n ,startdate,isactive order by startdate) t");
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			pstmt = DB.prepareStatement(sqlSel.toString(), null);
			for(int n=0; n<fields.length; n++){
				pstmt.setObject(n+1, po.get_Value(fields[n]));
			}
			rs = pstmt.executeQuery();
			while(rs.next()){
				StringBuilder sqlUpd = new StringBuilder("update ");
				sqlUpd.append(po.get_TableName()).append(" set enddate=?");
				sqlUpd.append(", isactive=? ");
				sqlUpd.append(", processed=? ");
				StringBuilder sqlWhereUpd = new StringBuilder(" where startdate=? ");

				for(int k=0; k<fields.length; k++){
					sqlWhereUpd.append(" and ").append(fields[k]).append("= ").append(rs.getObject(fields[k]));
				}
				sqlUpd.append(sqlWhereUpd);
				Timestamp edt = rs.getTimestamp("enddate");
				long ti = edt.getTime() - 1;
				edt = new Timestamp (ti);
				DB.executeUpdateEx(sqlUpd.toString(), new Object[]{edt, rs.getString("isactive"), rs.getString("processed"), rs.getTimestamp("startdate")},po.get_TrxName());
			}
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
	}
	
	/**
	 * Check overlapping dates
	 * 
	 * @param po  			Model
	 * @param listField     listField If two or more fields Write the via , Exsample: 'column1,column2'
	 * @return
	 */
	public static boolean isOverlay(PO po, String listField){
		
		int count = 0;
		StringBuilder sql_ = new StringBuilder("select count(*) from ");
		sql_.append(po.get_TableName());
		
		StringBuilder sqlWhere = new StringBuilder(" where startdate >= ? \n");
		String[] fields = listField.split(",");
		//
		if(fields.length < 1)
			return false;
		
		for(int i=0; i<fields.length;i++){
			if(!fields[i].isEmpty()){
				sqlWhere.append("and ").append(fields[i]).append("=? \n");
			}
		}
		sql_.append(sqlWhere);
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{
			pstmt = DB.prepareStatement(sql_.toString(), null);
			pstmt.setTimestamp(1, (Timestamp) po.get_Value("startdate"));
			for(int n=0; n<fields.length; n++){
				pstmt.setObject(n+2, po.get_Value(fields[n]));
			}
			rs = pstmt.executeQuery();
			if(rs.next())
				count = rs.getInt(1);
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return (count > 0) ? true: false;
	}

	private static String[] arrMonthNameRu = {"", "январь", "февраль", "март", "апрель", "май", "июнь", "июль", "август", "сентябрь", "октябрь", "ноябрь", "декабрь"};
    private static String[] arrMonthNameRuCase = {"", "января", "февраля", "марта", "апреля", "мая", "июня", "июля", "августа", "сентября", "октября", "ноября", "декабря"};
    private static String[] arrMonthNameKz = {"", "қаңтар", "ақпан", "наурыз", "сәуір", "мамыр", "маусым", "шілде", "тамыз", "қыркүйек", "қазан", "қараша", "желтоқсан"};
    private static String[] arrMonthNameKzCase = {"", "қаңтар", "ақпан", "наурыз", "сәуір", "мамыр", "маусым", "шілде", "тамыз", "қыркүйек", "қазан", "қараша", "желтоқсан"};
    
    private static String[] arrMonthNameEn = {"", "january", "february", "march", "april", "may", "june", "july", "august", "september", "october", "november", "december"};
    private static LinkedHashMap<String, String[]> map = new LinkedHashMap<String, String[]>();
    private static final String case_ = "_CASE";
    private static final String lanRu = Language.AD_Language_ru_RU;
    private static final String lanEn = Language.AD_Language_en_US;
    private static final String lanKz = Language.AD_Language_kz_KZ;
    
    /**
     * method which helps manipulating the entered date and getting the name of month
     * @param dt Timestamp parameter, provides formatting and parsing the entered date
     * @param ln String of language, used to translate the month to required language 
     * @param c boolean parameter, used to check the grammatical properties of month's name 
     * @return String (name of required month)
     */
    public static String getMonthName(Timestamp dt, String ln, boolean c) {

        DateTime dd = new DateTime(dt.getTime());
        String key = ln;

        // map used for getting result by checking language and using appropriate array of String
        // case used for grammatical properties of month's name
        map.put(lanRu+case_, arrMonthNameRuCase);
        map.put(lanRu, arrMonthNameRu);
        map.put(lanEn, arrMonthNameEn);
        map.put(lanEn+case_, arrMonthNameEn);
        map.put(lanKz, arrMonthNameKz);
        map.put(lanKz+case_, arrMonthNameKzCase);
        if (c) 
            key += case_;

        String[] arr = map.get(key);
        String result = arr[dd.getMonthOfYear()];

        return result;

    }
    
    public static MAttachmentEntry getAttachment(ProcessInfo pi, Properties m_ctx, String fileName){
		
		MProcess process = new MProcess(Env.getCtx(),  pi.getAD_Process_ID(), pi.getTransactionName());
		MAttachment attachment = process.getAttachment();
		
		if(attachment == null)
			throw new AdempiereException(Msg.translate(m_ctx, "AttachmentNotFound"));
		//
		MAttachmentEntry[] list = attachment.getEntries();
		if(list.length == 1)
			return list[0];
		//
		MAttachmentEntry entry = null;
		for(MAttachmentEntry a: attachment.getEntries()){
			if(fileName.isEmpty() || fileName == null) {
				entry = a;
				break;
			}else if (a.getName().substring(0, a.getName().indexOf(".")) .equals(fileName)) {
				entry = a;
				break;
			}
		}

		return entry;
	}
    
    /**
	 * Download db attachment to local file
	 * @param entry
	 * @return File
	 */
	public static File getAttachmentEntryFile(MAttachmentEntry entry) {
		String localFile = localFilePath + entry.getName();
		String downloadedLocalFile = localFilePath +"TMP" + entry.getName();
		File reportFile = new File(localFile);
		if (reportFile.exists()) {
			String localMD5hash = DigestOfFile.GetLocalMD5Hash(reportFile);
			String entryMD5hash = DigestOfFile.getMD5Hash(entry.getData());
			if (localMD5hash.equals(entryMD5hash))
			{
				log.info(" no need to download: local report is up-to-date");
			}
			else
			{
				log.info(" report on server is different that local one, download and replace");
				File downloadedFile = new File(downloadedLocalFile);
				entry.getFile(downloadedFile);
				if (! reportFile.delete()) {
					throw new AdempiereException("Cannot delete temporary file " + reportFile.toString());
				}
				if (! downloadedFile.renameTo(reportFile)) {
					throw new AdempiereException("Cannot rename temporary file " + downloadedFile.toString() + " to " + reportFile.toString());
				}
			}
		} else {
			entry.getFile(reportFile);
		}
		return reportFile;
	}
	
	/**
	 * The cell to start
	 * @param book
	 * @param delimiter
	 * @return
	 */
	public static ExcelCell getCellStart(Workbook book, String delimiter){
		
		ExcelCell cell = new ExcelCell();
		Sheet sheet =  book.getSheet(0);
		String s = "";
		
		for(int i=0; i < sheet.getColumns(); i++){
			for(int j=0; j < sheet.getRows(); j++){
				s = sheet.getCell(i, j).getContents().toLowerCase();
                if (s.isEmpty() || !s.equals(delimiter)) {
                    continue;
                }
                cell.setC(i);
                cell.setR(j);
                cell.setV(s);
                cell.setType("String");
			}
			
		}
		
		return cell;
		
	}
	
	public static HSSFCell getCellStart(HSSFSheet sheet, HSSFWorkbook wb, String marker) {
		HSSFCell retCell = null;
		
		for(int i = 0; i < 20; i++) {
			HSSFRow row = sheet.getRow(i);
			if(row == null)
				break;
			for(int j = 0; j < 10; j++) {
				HSSFCell cell = row.getCell(j);
					
				if(cell != null && cell.getCellType() != 0 && cell.getStringCellValue().equals(marker)) {					
					return cell;
				}
			}
		}
		return retCell;
	}
	
	/**
	 * Calculate hours between start time and end times
	 * @param starttime
	 * @param endtime
	 */	
	public static int getHours(Timestamp starttime, Timestamp endtime){

		return (int) (Math.abs(starttime.getTime() - endtime.getTime()) / (60 * 60 * 1000));
	}
	
	/**
	 *	Open the temporary file with the application associated with the extension in the file name
	 *	@return true if file was opened with third party application
	 */
	public static boolean launchFile (File fileName)
    {
        try 
        {

            if (Env.isWindows())
            {
            	Runtime.getRuntime().exec("rundll32 SHELL32.DLL,ShellExec_RunDLL \"" + fileName + "\"");
                return true;
            }
            else if (Env.isMac())
            {
            	String [] cmdArray = new String [] {"open", fileName.getAbsolutePath()};
            	Runtime.getRuntime ().exec (cmdArray);
                return true;
            }
            else	//	other OS. originally nothing here. add the following codes
            {
            	try {
            		Desktop desktop = null;
            		if (Desktop.isDesktopSupported()) {
            			desktop = Desktop.getDesktop();
            			File file = new File(fileName.getAbsolutePath());
            			desktop.open(file);
            			return true;
            		}               
            	} catch (IOException e) {
            		e.printStackTrace();
            	}
            }
        } 
        catch (Exception e) 
        {
        	log.log(Level.SEVERE, "", e);
        }
        return false;
    }    //    openFile
	
	
	/**
	 * Calculate interval between current days and employees start days and end days, true if less some value 
	 * @param C_BPartner_ID
	 * @param dt
	 * @return
	 */
	
	public static boolean is15Days(int C_BPartner_ID, Timestamp dt){
		
		if(C_BPartner_ID == 0)
    		return false;
				
		Timestamp sdt = X_HR_Employee.getMinStartDate(C_BPartner_ID, null);
		Timestamp edt = X_HR_Employee.getMaxEndDate(C_BPartner_ID, null);	
		
		
		DateTime current = new DateTime(dt.getTime());
		DateTime start = new DateTime(sdt.getTime());
		DateTime end = new DateTime(edt.getTime());
		
		// hiring	
			
			int hiring = Mhrmtimesheet.getHiringDay(C_BPartner_ID, sdt);
			int dismiss = Mhrmtimesheet.getDismissalDay(C_BPartner_ID, edt);
		
		if(
			(current.getYear() == start.getYear() && current.getMonthOfYear() == start.getMonthOfYear() && (hiring > 0 || hiring < 16))
		  || 
			(current.getYear() == end.getYear() && current.getMonthOfYear() == end.getMonthOfYear() && (dismiss > 0 || dismiss < 16))
		  )
			return true;
		else
			return false;	
	}
	
	/** */
	public static MBPartner getOrgInfo(){
		
		MOrg org = new MOrg(Env.getCtx(), Env.getAD_Org_ID(Env.getCtx()),null);
		MBPartner bp = new MBPartner(Env.getCtx(), org.getLinkedC_BPartner_ID(null), null);
		
		return  bp;
	}
	
	
	/**
	 *  Get period_id 
	 *  @param dt
	 *  @return result
	 */
	
	public static X_C_Period getPeriod(Timestamp dt){
				
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		X_C_Period result = null;
		
		// Employee data from the card
		String sql_ = " SELECT * FROM C_PERIOD WHERE " + DB.TO_DATE(dt, true) + " BETWEEN STARTDATE AND ENDDATE ";
		try
		{
			pstmt = DB.prepareStatement(sql_,null);
			rs = pstmt.executeQuery();
			if(rs.next())
				result = new X_C_Period(Env.getCtx(), rs, null);				
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}	
		
		return result;
	}
	
	/**
	 * Send Mail
	 * @param userToID   (AD_User_ID)
	 * @param userFromID (AD_User_ID)
	 * @param label
	 * @param message
	 * @return
	 * @throws Exception
	 */
	public static boolean sendMail(int userToID, int userFromID, String label, String message, boolean isHTML) throws Exception
	{
		MUser userFrom = new MUser(Env.getCtx(), userFromID, null);
		MUser userTo = new MUser(Env.getCtx(), userToID, null);

		MClient client = new MClient(Env.getCtx(), Env.getAD_Client_ID(Env.getCtx()), null);
		
		EMail email = client.createEMail(userFrom, userTo, label, message);	
		if (!email.isValid())
		{
			log.info (" @RequestActionEMailError@ Invalid EMail: " + userToID);
			throw new AdempiereException(" @RequestActionEMailError@ Invalid EMail: " + userToID);
		}
		if (isHTML)
			email.setMessageHTML(label, message);
		else
		{
			email.setSubject (label);
			email.setMessageText (message);
		}
		
		try 
		{
			EMail.SENT_OK.equals(email.send());
		}
		catch(Exception e)
		{
			log.log(Level.SEVERE, e.toString());
		}
		
		return true;
	}
	
	public static String getHostName(){
		
		InetAddress localhost = null;
		String hostName = UNKNOWN;
		try
		{
			localhost = InetAddress.getLocalHost();
			hostName = localhost.getHostName();
		}
		catch (Exception e)
		{
			log.severe("Cannot get server host name");
		}
		
		return hostName;
	}
	
	public static String getHostHttp(){
		return "http://" + getHostName(); 
	}
	public static Timestamp getDismissalEndDate(Timestamp dismissalDate, int calendarDaysCount){
		
		Calendar startDate = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
		Calendar endDate = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
		startDate.setTime(dismissalDate);
		endDate.setTime(dismissalDate);
		endDate.add(Calendar.DAY_OF_MONTH, calendarDaysCount+5);
		
		System.out.println(startDate.getTime());
		System.out.println(endDate.getTime());
		
		String sql = "SELECT count(*) FROM hrm_workschedule WHERE hrm_DateFrom BETWEEN  '" +
				startDate.getTime() +
				"' and  '" +
				endDate.getTime() +
				"' AND hrm_holidays_id IS NOT NULL";
		
		int holidays = DB.getSQLValue(null, sql);
		
		//endDate.add(Calendar.DAY_OF_MONTH, 1);
		
		while (isHoliday(new Timestamp(endDate.getTimeInMillis()))){
			endDate.add(Calendar.DAY_OF_MONTH, 1);
		}
		
		endDate.add(Calendar.DAY_OF_MONTH, holidays);
		
		System.out.println(endDate.getTime());
		
		return new Timestamp(endDate.getTimeInMillis());
	}
	
	/**
	 * To find out the process ID
	 * @param likeName
	 * @return
	 */
	public static int getAD_Process(String likeName){
		
		int AD_Process_ID = 0;
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		// The name must start with a report 'likeName'
		String sql_ = "select distinct t.ad_process_id from ad_process t "+ 
					  "where (lower(t.value) like lower('%"+likeName+"%') or lower(t.name) like lower('%"+likeName+"%')) "+ 
				      "and (lower(t.value) like lower('%"+likeName+"%') or lower(t.name) like lower('%"+likeName+"%'))";
		try
		{
			pstmt = DB.prepareStatement(sql_, null);
			rs = pstmt.executeQuery();
			if (rs.next())
				return rs.getInt(1);
		}
		catch (SQLException e)
		{
			log.log(Level.SEVERE, "product", e);
		}
		finally
		{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return AD_Process_ID;
		
	}
	/**
	 * poi 3.9 XSSFWorkbook
	 * @param rsPath attachment file name
	 * @param rsPathLenght attachment file name lenght
	 * @return full path if file name already exists then fullpath+
	 */
	public static String getResultPath(String rsPath, int rsPathLenght){
		  String resultPath = rsPath ;
		  Integer pathI = 0;
		  boolean flag = true ;
		  while (flag) {
		   File f = new File(Util.localFilePath + resultPath + ".xlsx");
		   try {
		    if (f.exists() && !f.delete()) {
		    	resultPath = resultPath + "(" ;
		    	resultPath = resultPath.substring(0, rsPathLenght) + (++pathI) + ")";
		    } else {
		     flag = false;
		    }
		   } catch (Exception e) {
		    e.printStackTrace();
		    resultPath = resultPath + "(" + (++pathI) + ")";
		   }
		  }
		  resultPath = Util.localFilePath + resultPath + ".xlsx" ;
		return resultPath;
	}
	
	public static boolean checkString(String string) {
        try {
        	Double.parseDouble(string);
        } catch (Exception e) {
            return false;
        }
        return true;
    }
	public static boolean isDigit(String s) throws NumberFormatException {
	    try {
	        Integer.parseInt(s);
	        return true;
	    } catch (NumberFormatException e) {
	        return false;
	    }
	}
}
